﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using Jayden.Dll.Core.Persistence.Attributes;
using Jayden.Dll.Core.Persistence.Models;
using Jayden.Dll.Core.Persistence.Queries;

namespace Jayden.Dll.Core.Persistence.Tools
{
	public class SQLHelper
	{
		public static readonly SQLHelper Instance = new SQLHelper();

		public static DbType GetDefaultDbType(Type type)
		{
			if (type.IsGenericType && type.GetGenericTypeDefinition() == typeof(Nullable<>))
				return GetDefaultDbType(Nullable.GetUnderlyingType(type));
			DbType dbType;
			if (Enum.TryParse<DbType>(type.Name, out dbType))
				return dbType;
			if (type.IsEnum)
				return DbType.String;
			if (type == typeof(byte[]))
				return DbType.Binary;
			throw new ArgumentException(string.Format("Invalid Type to convert to DbType: '{0}'.", type.FullName));
		}

		public string GenerateCreateTable(Entity entity)
		{
			string tableName = entity.Table.TableName;
			List<string> sqlFields = new List<string>();
			foreach (Property property in entity.Properties)
				sqlFields.Add(GenerateField(property.Field, entity.PrimaryKey));
			string sqlPk = GeneratePrimaryKey(entity.PrimaryKey);
			if(sqlPk != null)
				sqlFields.Add(sqlPk);
			string sqlCreateTable = string.Format("CREATE TABLE {0} ({1})", tableName, string.Join(", ", sqlFields));
			return sqlCreateTable;
		}

		public string GenerateInsertInto(string tableName, string[] fields, string[] values)
		{
			return string.Format("INSERT INTO {0} ({1}) VALUES ({2})", tableName, string.Join(", ", fields), string.Join(", ", values));
		}

		public string GenerateInsertInto(Entity entity)
		{
			string tableName = entity.Table.TableName;
			List<string> sqlFields = new List<string>();
			List<string> sqlValues = new List<string>();
			foreach (Property property in entity.Properties)
			{
				if (!property.Field.AutoIncrement)
				{
					sqlFields.Add(property.Field.FieldName);
					sqlValues.Add("@" + property.Field.FieldName);
				}
			}
			string sqlInsertInto = GenerateInsertInto(tableName, sqlFields.ToArray(), sqlValues.ToArray());
			return sqlInsertInto;
		}

		public virtual string GeneratePrimaryKey(Index pk)
		{
			if (pk.Length == 0)
				return null;
			List<string> fields = new List<string>();
			foreach (Property p in pk)
				fields.Add(p.Name);
			return string.Format("PRIMARY KEY ({0})", string.Join(", ", fields));
		}

		public virtual string GenerateField(Field field, Index pk)
		{
			string name = field.FieldName;
			string type = GenerateType(field.DbType);
			List<string> constraints = new List<string>();
			if (field.AutoIncrement)
				constraints.Add("AUTO_INCREMENT");
			if (field.Unique)
				constraints.Add("UNIQUE");
			if (field.DefaultValue != null)
				constraints.Add(string.Format("DEFAULT {0}", GetFormattedDefaultValue(field)));
			if(!field.Nullable)
				constraints.Add("NOT NULL");
			string fieldDef = string.Format("{0} {1} {2}", name, type, string.Join(" ", constraints));

			return fieldDef.Trim();
		}

		public virtual string GetFormattedDefaultValue(Field field)
		{
			if (field.DefaultValue == null)
				return "NULL";
			return string.Format("'{0}'", field.DefaultValue);
		}

		public virtual string GenerateType(DbType type)
		{
			switch (type)
			{
				case DbType.AnsiString:
				case DbType.AnsiStringFixedLength:
				case DbType.String:
				case DbType.StringFixedLength:
					return "text";
				case DbType.Single:
				case DbType.Double:
					return "decimal";
				case DbType.Time:
				case DbType.Date:
				case DbType.Decimal:
				case DbType.DateTime:
					return "datetime";
				case DbType.Boolean:
				case DbType.Byte:
				case DbType.SByte:
					return "tinyint";
				case DbType.UInt16:
				case DbType.Int16:
					return "smallint";
				case DbType.UInt32:
				case DbType.Int32:
					return "int";
				case DbType.Int64:
				case DbType.UInt64:
					return "bigint";
				case DbType.Binary:
					return "binary";
				default:
					throw new NotImplementedException(string.Format("DbType '{0}' cannot be generated for this DBMS.", type));
			}
		}

		public string GenerateCount(string from, string where, string[] groupBy)
		{
			return GenerateSelect(new string[] { "COUNT(*)" }, from, where, groupBy, null, null, null);
		}

		public string GenerateSelect(string[] select, string from, string where, string[] groupBy, string[] orderBy, int? limit, int? offset)
		{
			return GenerateSelect(select, new string[] { from }, where, groupBy, orderBy, limit, offset);
		}
		public virtual string GenerateSelect(string[] select, string[] from, string where, string[] groupBy, string[] orderBy, int? limit, int? offset)
		{
			List<string> parts = new List<string>();
			parts.Add("SELECT");
			if (select == null || select.Length <= 0)
				parts.Add("*");
			else
				parts.Add(string.Join(",", select));
			parts.Add("FROM");
			parts.Add(string.Join(",", from));
			if (where != null && !string.IsNullOrWhiteSpace(where))
			{
				parts.Add("WHERE");
				parts.Add(where);
			}
			if (groupBy != null && groupBy.Length > 0)
			{
				parts.Add("GROUP BY");
				parts.Add(string.Join(",", groupBy));
			}
			if (orderBy != null && orderBy.Length > 0)
			{
				parts.Add("ORDER BY");
				parts.Add(string.Join(",", groupBy));
			}
			if (limit.HasValue || offset.HasValue)
				throw new NotImplementedException();
			return string.Join(" ", parts);
		}

		public string GenerateDelete(string from, string where)
		{
			return string.Format("DELETE FROM {0} WHERE {1}", from, where);
		}
		public string GenerateDelete(Entity entity)
		{
			string tableName = entity.Table.TableName;
			List<string> sqlPks = new List<string>();
			foreach (Property property in entity.PrimaryKey)
				sqlPks.Add(property.Field.FieldName + "=@" + property.Field.FieldName);
			string sqlDelete = GenerateDelete(tableName, string.Join(" AND ", sqlPks));
			return sqlDelete;
		}

		public string GenerateUpdate(string tableName, string[] assignations, string where)
		{
			if(where == null)
				return string.Format("UPDATE {0} SET {1}", tableName, string.Join(", ", assignations));
			return string.Format("UPDATE {0} SET {1} WHERE {2}", tableName, string.Join(", ", assignations), where);
		}

		public string GenerateUpdate(Entity entity)
		{
			string tableName = entity.Table.TableName;
			List<string> sqlFields = new List<string>();
			List<string> sqlPks = new List<string>();
			foreach (Property property in entity.Properties)
				if(!property.Field.AutoIncrement)
					sqlFields.Add(property.Field.FieldName + "=@" + property.Field.FieldName);
			foreach (Property property in entity.PrimaryKey)
				sqlPks.Add(property.Field.FieldName + "=@" + property.Field.FieldName);
			string sqlUpdate = GenerateUpdate(tableName, sqlFields.ToArray(), string.Join(" AND ", sqlPks));
			return sqlUpdate;
		}

		public string GenerateSelect(Entity entity, IEnumerable<Property> whereClause) { return GenerateSelect(entity, whereClause.ToArray()); }
		public string GenerateSelect(Entity entity, params Property[] whereClause)
		{
			string tableName = entity.Table.TableName;
			List<string> sqlFields = new List<string>();
			List<string> sqlWhere = new List<string>();
			foreach (Property property in entity.Properties)
				sqlFields.Add(property.Field.FieldName);
			foreach (Property property in whereClause)
				sqlWhere.Add(property.Field.FieldName + "=@" + property.Field.FieldName);
			string sqlSelect = GenerateSelect(sqlFields.ToArray(), tableName, string.Join(" AND ", sqlWhere), null, null, null, null);
			return sqlSelect;
		}
		public string GenerateSelect(Entity entity)
		{
			return GenerateSelect(entity, entity.PrimaryKey);
		}

		public virtual string GenerateQuery(Query query, int? limit, int? offset)
		{
			if (limit.HasValue || offset.HasValue)
				throw new NotImplementedException();
			return query.ToString();
		}
	}
}
